<?php

class ModelToolPricexls extends Model {

    //приоритет в названиях за 1с
    private $priority;
    private $objPHPExcel;
    private $excelNum;

    function __construct($registry) {

        parent::__construct($registry);

        $this->registry->get('load')->library('excel/PHPExcel');

        $this->load->model('catalog/category');
        $this->load->model('catalog/product');
        ////////////////////////////////////////////////////
        $this->objPHPExcel = new PHPExcel();
        //$this->registry->get('load')->library('excel/class.writeexcel_worksheet.inc');
        //$this->priority = $this->registry->get('config')->get('exchange_priority');
    }

    public function pathTree($parent_id = 0, $prefix = "") {


        // global $out;
        $sql = "SELECT * FROM " . DB_PREFIX . "category c LEFT JOIN " . DB_PREFIX . "category_description d ON (c.category_id=d.category_id) WHERE parent_id = '$parent_id'";

        $query = $this->db->query($sql);

        if (empty($query->row)) {
            return;
        }
        foreach ($query->rows as $count => $row) {

            //   $out .= $prefix . $row['name'] . "<br>";
            $this->toExcel($row['category_id'], $row['name'], $parent_id);
            $this->pathTree($row['category_id'], $prefix . "&nbsp;&nbsp;");
        }


        return;
    }

    private function toExcel($cat_id, $cat_name, $parent = 0) {

        $cat_name = html_entity_decode($cat_name);

        //$this->objPHPExcel->getActiveSheet()->getColumnDimension('A')->setAutoSize(true);
        //$this->objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
        $this->objPHPExcel->getActiveSheet()->setShowSummaryBelow(false);
        $this->objPHPExcel->getActiveSheet()->setTitle('Все товары');




        if ($parent === 0) {

           //   $this->objPHPExcel->getActiveSheet()->insertNewRowBefore($this->excelNum,1);

            $this->objPHPExcel->getActiveSheet()->getStyle('A' . $this->excelNum)->getFont()->setSize(14);
            $this->objPHPExcel->getActiveSheet()->getStyle('A' . $this->excelNum)->getFont()->setBold(true);
            $this->objPHPExcel->getActiveSheet()->getStyle('A' . $this->excelNum)->getFont()->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE);
            $this->objPHPExcel->getActiveSheet()->getStyle('A' . $this->excelNum)->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLUE);




            $this->objPHPExcel->setActiveSheetIndex(0)
                    ->setCellValue('A' . $this->excelNum, $cat_name);
            
              //$this->objPHPExcel->getActiveSheet()->getRowDimension($this->excelNum)->setOutlineLevel(1);
              //$this->objPHPExcel->getActiveSheet()->getRowDimension($this->excelNum)->setVisible(false);
              //$this->objPHPExcel->getActiveSheet()->getRowDimension($this->excelNum)->setCollapsed(true);
             
        } else {

          //   $this->objPHPExcel->getActiveSheet()->insertNewRowBefore($this->excelNum,1);
            
            

            $this->objPHPExcel->getActiveSheet()->getStyle('A' . $this->excelNum)->getFont()->setSize(12);
            $this->objPHPExcel->getActiveSheet()->getStyle('A' . $this->excelNum)->getFont()->setBold(true);
            $this->objPHPExcel->getActiveSheet()->getStyle('A' . $this->excelNum)->getFont()->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE);
            $this->objPHPExcel->getActiveSheet()->getStyle('A' . $this->excelNum)->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_DARKGREEN);

            $this->objPHPExcel->setActiveSheetIndex(0)
                    ->setCellValue('A' . $this->excelNum, $cat_name);


            $this->objPHPExcel->getActiveSheet()->getRowDimension($this->excelNum)->setOutlineLevel(1);
            $this->objPHPExcel->getActiveSheet()->getRowDimension($this->excelNum)->setVisible(false);
            $this->objPHPExcel->getActiveSheet()->getRowDimension($this->excelNum)->setCollapsed(true);
        }


        $this->excelNum++;

        $product_by_cat = $this->model_catalog_product->getProductsByCategoryId((int) $cat_id);

        $product_options=array();
        
        foreach ($product_by_cat as $key => $val) {

           //  $this->objPHPExcel->getActiveSheet()->insertNewRowBefore($this->excelNum,1);
            $product_options = $this->model_catalog_product->getProductOptions($val['product_id']);


            $this->objPHPExcel->setActiveSheetIndex(0)                   
                    ->setCellValue('A' . $this->excelNum, html_entity_decode($val['name']))
                    ->setCellValue('B' . $this->excelNum, $val['model'])
                    ->setCellValue('C' .  $this->excelNum, $product_options[0]['option_value'])
                    ->setCellValue('D' .  $this->excelNum, $val['price'])
                    ;
            
           
            
            $this->objPHPExcel->getActiveSheet()->getRowDimension($this->excelNum)->setOutlineLevel(1);
            $this->objPHPExcel->getActiveSheet()->getRowDimension($this->excelNum)->setVisible(false);
            $this->objPHPExcel->getActiveSheet()->getRowDimension($this->excelNum)->setCollapsed(true);


            $this->excelNum++;
        }
        return;
    }

    public function getExcelFile() {





        set_time_limit(0);


        
          $this->objPHPExcel->getProperties()->setCreator("www.from64.ru")
          ->setLastModifiedBy("www.from64.ru")
          ->setTitle("Прайс-лист")
          ->setSubject("Прайс-лист")
          ->setDescription("Прайс-лист сгенерированный www.from64.ru")
          ->setKeywords("office price php")
          ->setCategory("price");
         
          $data = date("j.m.Y", time());
          
/////////////////////Шапка///////////////////////////
          $this->objPHPExcel->getActiveSheet()->mergeCells('A1:D1');
          $this->objPHPExcel->getActiveSheet()->getStyle('A1:D1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
          $this->objPHPExcel->getActiveSheet()->setCellValue('A1', 'Прайс-лист от '. $data);
          
          $this->objPHPExcel->getActiveSheet()->mergeCells('A3:D3');
          $this->objPHPExcel->getActiveSheet()->getStyle('A3:D3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
          $this->objPHPExcel->getActiveSheet()->getStyle('A3')->getFont()->setSize(26);
          $this->objPHPExcel->getActiveSheet()->getStyle('A3')->getFont()->setBold(true);
          $this->objPHPExcel->getActiveSheet()->getStyle('A3:D3')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLUE);
          $this->objPHPExcel->getActiveSheet()->setCellValue('A3', 'Торговая компания "Все в дом"');
          
          $style_header = array( 
              //'borders' => array( 'bottom' => $default_border, 
              //'left' => $default_border,
              //'top' => $default_border, 
              //'right' => $default_border, ), 
              'fill' => array( 'type' => PHPExcel_Style_Fill::FILL_SOLID,
                  'color' => array('rgb'=>'FFCC33'),
                  ), 
              'font' => array( 'bold' => true, )
              
              );  
          
          $this->objPHPExcel->getActiveSheet()->getStyle('A9:D9')->applyFromArray( $style_header );
           //$this->objPHPExcel->getActiveSheet()->getStyle('A9:D9')->getFill()->getStartColor()->setRGB('FF0000');
           $this->objPHPExcel->getActiveSheet()->getStyle('A9:D9')->getFont()->setSize(11);
           $this->objPHPExcel->getActiveSheet()->setCellValue('A9', 'Наименование');
           $this->objPHPExcel->getActiveSheet()->setCellValue('B9', 'Артикул');
           $this->objPHPExcel->getActiveSheet()->setCellValue('C9', 'Ед. изм.');
           $this->objPHPExcel->getActiveSheet()->setCellValue('D9', 'Цена, руб.');
          
          
          $this->objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(11);
          $this->objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
          //$this->objPHPExcel->getActiveSheet()->setCellValue('D1', PHPExcel_Shared_Date::PHPToExcel(time()));
          //$this->objPHPExcel->getActiveSheet()->getStyle('A1')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDD2);
          $this->objPHPExcel->getActiveSheet()->getColumnDimension('A')->setAutoSize(true);
          $this->objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
          $this->objPHPExcel->getActiveSheet()->getColumnDimension('C')->setAutoSize(true);
          $this->objPHPExcel->getActiveSheet()->getColumnDimension('D')->setAutoSize(true);
          
          
/////////////////////////////////////////////////////          
          
       // $objReader = PHPExcel_IOFactory::createReader('Excel5');
        //$this->objPHPExcel = $objReader->load(DIR_SYSTEM . "library/excel/templates/30template.xls");


        $this->excelNum = 10;


        
        $this->pathTree();
        
        
        
        
        
        
       // $this->toExcel(1346, "Ололо", $parent = 0);
        
       /* 
        $product_by_cat = $this->model_catalog_product->getProductsByCategoryId(1346);

        foreach ($product_by_cat as $key => $val) {

             $this->objPHPExcel->getActiveSheet()->insertNewRowBefore($this->excelNum,1);



            $this->objPHPExcel->setActiveSheetIndex(0)
                    ->setCellValue('A' . $this->excelNum, html_entity_decode($val['name']))
                    ->setCellValue('B' . $this->excelNum, $val['price']);
            
            
            
           $this->objPHPExcel->getActiveSheet()->getRowDimension($this->excelNum)->setOutlineLevel(1);
           $this->objPHPExcel->getActiveSheet()->getRowDimension($this->excelNum)->setVisible(false);
           $this->objPHPExcel->getActiveSheet()->getRowDimension($this->excelNum)->setCollapsed(true);


            $this->excelNum++;
        }
        
        
        */
        
        
        
        



        $objWriter = PHPExcel_IOFactory::createWriter($this->objPHPExcel, 'Excel5');
        $objWriter->save(str_replace('.php', '.xls', DIR_DOWNLOAD . 'price.php'));
        ///////////////////////////////////////////////////////////////////////////////////////////////
        
          $zip = new ZipArchive;
          $zip->open(DIR_DOWNLOAD . 'price.zip', ZIPARCHIVE::CREATE);
          $zip->addFile(DIR_DOWNLOAD . 'price.xls', 'price.xls');
          $zip->close();

          unlink(DIR_DOWNLOAD . 'price.xls');
         //////////////////////////////////////////////////////////////////////////////////////////////
    }

}

?>
